from fileinput import filename
import time
from datetime import datetime, timedelta, timezone
from distutils import file_util
from distutils.command.upload import upload
from genericpath import isdir
import os
# from os import startfile
import config
import uuid
import re
import openpyxl
import mysql.connector

# set beijing time as default utc_time
SHA_TZ = timezone(
    timedelta(hours=8),
    name='Asia/Shanghai',
)
utc_now = datetime.utcnow().replace(tzinfo=timezone.utc)

data_list = list()

def insert_picture_to_tbl_picture_file(file_path):

    cnx = None
    cursor = None
    try:
        cnx = mysql.connector.connect(**config.kkomia_picture_db)
        cursor = cnx.cursor()
    except Exception as e:
        print("Connected failed:" + str(e))
        if cursor:
            cursor.close()
        if cnx:
            cnx.close()
    print("Conneted to kkomia_picture_db Database")

    # open picture file and save temp file
    is_valid_file = True
    
    try:
        picture_name = str(re.split('\/', file_path)[-1])
        picture_format = str(re.split('\.', picture_name)[-1])
        picture_name = str(re.split('\.', picture_name)[0])
        
        print(picture_format)

        upload = open(file_path, "rb")
        file_uuid = str(uuid.uuid4())
        
        if not os.path.isdir(os.getcwd() + r"/temp"):
            os.mkdir(os.getcwd() + r"/temp/")

        temp_file_path = os.getcwd() + os.path.join(r"/temp/", file_uuid) + '~'

        print("Temp picture will save to %s", temp_file_path)
        raw_blob = upload.read()
        open(temp_file_path, 'wb').write(raw_blob)
        print("Save temp picture successfully")
        
    except Exception as e:
        print("Open picture and and save temp file error")
        is_valid_file = False

    if is_valid_file:
        try:
            add_values = ("INSERT INTO tbl_picture_file (picture_name, picture_format, utc_date_time, file_object) "
                        "VALUES ( "
                        "%s, %s, %s, %s)")
            print("insert sql is:%s", add_values)

            cursor.execute(add_values, (picture_name, picture_format, datetime.utcnow(), raw_blob))
            cnx.commit()
            cursor.close()
            cnx.close()

            print("Insert picture successfully...")

        except Exception as e:
            print("Corsor.execute failed:" + str(e))
            if cursor:
                cursor.close()
            if cnx:
                cnx.close()
